package com.webapp.model;

import java.sql.DriverManager;
import java.sql.ResultSet;
import java.util.ArrayList;

import com.mysql.jdbc.Connection;
import com.mysql.jdbc.Statement;

/*
 * Class used to connect to the database and gather each application, store it in an ArrayList of 
 * applications and return this list to be displayed.
 */
public class ViewTableModel{
	
	private static Connection conn = null;
	private static String driver = "com.mysql.jdbc.Driver";
	private static String dbUrl = "jdbc:mysql://mysql1.cs.stonybrook.edu:3306/gdonnelly";
	private static String dbPassword = "106338300";
	private static String dbUser = "gdonnelly";
	
	//list of applications to be used for a table of applications
	private static ArrayList<Application> appList;
	
	/*
	 * Data for View Table: 
	 * Name (first, middle, last), email, 
	 * research area of interest, UG institution, UG GPA, Grad institution, Grad GPA
	 * OTS scores, tier, summary, average rating, reviews, link to PDF file
	 * 
	 * USES APP ID AS A REFERENCE
	 */
	public static ArrayList<Application> getTable(){
		
		appList = new ArrayList<Application>(100);
		
		try{
			Statement stmt = connect();
			ResultSet rs;
			
			//gets Name, AppId and email from database, creates Application obj and adds to appList
			rs = stmt.executeQuery("select * from ay_applicant_general_info");
			while(rs.next()){
				Application app = new Application();
				app.setAppId(rs.getString(2));
				app.setFirstName(rs.getString(3));
				app.setMiddleName(rs.getString(5));
				app.setLastName(rs.getString(4));
				app.setEmail(rs.getString(1));
				
				appList.add(app);
			}
			
			//gets research area of interest
			rs = stmt.executeQuery("select * from ay_applicant_edu_background");
			while(rs.next()){
				String email = rs.getString(1);
				String area = rs.getString(3);
				//goes through all current applications
				for(int i = 0; i < appList.size(); i++){
					//determines which is the correct application, based on email
					if(appList.get(i).getEmail().equals(email)){
						//sets research area of interest
						appList.get(i).setResearchArea(area);
						break;
					}
				}
			}
			
			//undergrad institution AND GPA
			rs = stmt.executeQuery("select * from ay_applicant_ug_insititute");
			while(rs.next()){
				String email = rs.getString(1);
				String inst = rs.getString(3);
				String gpa = rs.getString(4);

				for(int i = 0; i < appList.size(); i++){
					if(appList.get(i).getEmail().equals(email)){
						appList.get(i).setUgInst(inst);
						appList.get(i).setUgGpa(gpa);
						break;
					}
				}
			}
			
			//grad institution AND GPA
			rs = stmt.executeQuery("select * from ay_applicant_grad_insititution");
			while(rs.next()){
				String email = rs.getString(1);
				String inst = rs.getString(3);
				String gpa = rs.getString(4);
				
				for(int i = 0; i < appList.size(); i++){
					if(appList.get(i).getEmail().equals(email)){
						appList.get(i).setGradInst(inst);
						appList.get(i).setGradGpa(gpa);
						break;
					}
				}
			}
			
			//OTS gre scores (v, q, a, subj)
			rs = stmt.executeQuery("select * from ots_applicant_gre_score");
			while(rs.next()){
				String email = rs.getString(1);
				String greV = rs.getString(3);
				String greQ = rs.getString(5);
				String greA = rs.getString(7);
				String greSubj = rs.getString(9);
				
				for(int i = 0; i < appList.size(); i++){
					if(appList.get(i).getEmail().equals(email)){
						appList.get(i).setOfclGreV(greV);
						appList.get(i).setOfclGreQ(greQ);
						appList.get(i).setOfcileGreA(greA);
						appList.get(i).setOfclGreSubj(greSubj);
						break;
					}
				}
			}
			
			//OTS toefl scores (total, list, read, write, speak)
			rs = stmt.executeQuery("select * from ots_applicant_toefl_scores");
			while(rs.next()){
				String email = rs.getString(1);
				String total = rs.getString(3);
				String listen = rs.getString(4);
				String read = rs.getString(5);
				String speak = rs.getString(6);
				String write = rs.getString(7);
				
				for(int i = 0; i < appList.size(); i++){
					if(appList.get(i).getEmail().equals(email)){
						appList.get(i).setOfclToeflTotal(total);
						appList.get(i).setOfclToeflListen(listen);
						appList.get(i).setOfclToeflRead(read);
						appList.get(i).setOfclToeflSpeak(speak);
						appList.get(i).setOfclToeflWrite(write);
						break;
					}
				}
			}
			
			//tier, summary, average rating, number reviews
			rs = stmt.executeQuery("select * from app_general_info");
			while(rs.next()){
				String email = rs.getString(1);
				float tier = rs.getFloat(7);
				String summary = rs.getString(8);
				float avgRating = rs.getFloat(11);
				int numReviews = rs.getInt(10);
				String pdfUrl = rs.getString(12);
				
				for(int i = 0; i < appList.size(); i ++){
					if(appList.get(i).getEmail().equals(email)){
						appList.get(i).setTier(tier);
						appList.get(i).setSummary(summary);
						appList.get(i).setAvgRating(avgRating);
						appList.get(i).setNumDesiredReviews(numReviews);
						appList.get(i).setPdfUrl(pdfUrl);
					}
				}
			}
			
			//ratings and reviews
			rs = stmt.executeQuery("select * from app_reviews");
			while(rs.next()){
				String email = rs.getString(1);
				int reviewNum = rs.getInt(3);
				double rating = rs.getDouble(4);
				String review = rs.getString(5);
				
				for(int i = 0; i < appList.size(); i++){
					if(appList.get(i).getEmail().equals(email)){
						if(reviewNum == 1){
							appList.get(i).setRating1(rating);
							appList.get(i).setReview1(review);
						}
						else if(reviewNum == 2){
							appList.get(i).setRating2(rating);
							appList.get(i).setReview2(review);
						}
						else if(reviewNum == 3){
							appList.get(i).setRating3(rating);
							appList.get(i).setReview3(review);
						}
						else if(reviewNum == 4){
							appList.get(i).setRating4(rating);
							appList.get(i).setReview4(review);
						}
						else if(reviewNum == 5){
							appList.get(i).setRating5(rating);
							appList.get(i).setReview5(review);
						}
						break;
					}
				}
			}
			
			//close the connection
			rs.close();
			stmt.close();
			conn.close();
			
			return appList;
		}
		catch(Exception ex){
			System.out.println("Could not connect to database");
			return null;
		}
	}
	
	public ArrayList<Application> getNeededApps(String email){
		ArrayList<Application> list = new ArrayList<Application>();
		try{
			ResultSet rs;
			Statement stmt = connect();
			
			rs = stmt.executeQuery("SELECT applicant_email FROM user_needs_review WHERE user_email = '" +email+ "'");
			
			//get all email address the USER needs to review
			ArrayList<String> appEmail = new ArrayList<String>();
			while(rs.next())
				appEmail.add(rs.getString(1));
			
			//get the full application for the specific applicant
			for(int i = 0; i < appEmail.size(); i++){
				list.add(ViewApplicationModel.getApplication(appEmail.get(i)));
			}
			
			return list;
		}catch(Exception ex){return null;}
	}
	
	/*
	 * Used for searching the table.
	 */
	public static ArrayList<Application> search(String value)
	{
		// Create a dummy storage.
		ArrayList<Application> list = new ArrayList<Application>();
		
		// Search for value either by name or by E-mail.
		for (Application app : appList)
		{
			// Simple search criteria for names.
			String fullname = app.getFirstName() + " " + app.getLastName();
			String firstname = app.getFirstName();
			String lastname = app.getLastName();
			
			if (fullname.equals(value) || firstname.equals(value) || lastname.equals(value))
			{
				list.add(app);
			} else if (app.getEmail().equals(value)) {
				// Simple search criteria for e-mail.
				list.add(app);
		
				return list;
			}
		}
		return list;
	}
		
	/*
	 * Sort using insertion sort. LOTS of redundant code (fix later).
	 */
	public static ArrayList<Application> insertionSort(String value1, String value2,
			ArrayList<Application> list)
	{
		if (list.size() <= 1)
		{
			return list;
		}
		
		// Determine cases.
		if (value1.equals("first_name"))
		{
			int i,j;
			String name;
			Application app;
			for (i = 1; i < list.size(); i++)
			{
				name = list.get(i).getFirstName();
				app = list.get(i);
				j = i;
				
				while(j > 0 && list.get(j - 1).getFirstName().compareTo(name) > 0)
				{
					Application element = list.get(j - 1);
					list.set(j, element);
					
					j--;
				}
				list.set(j, app);
			}
			
		} else if (value1.equals("last_name")) {
			int i,j;
			String name;
			Application app;
			for (i = 1; i < list.size(); i++)
			{
				name = list.get(i).getLastName();
				app = list.get(i);
				j = i;
				
				while(j > 0 && list.get(j - 1).getLastName().compareTo(name) > 0)
				{
					Application element = list.get(j - 1);
					list.set(j, element);
					
					j--;
				}
				list.set(j, app);
			}
		} else if (value1.equals("e-mail")) {
			int i, j;
			String name;
			Application app;
			for (i = 1; i < list.size(); i++) 
			{
				name = list.get(i).getEmail();
				app = list.get(i);
				j = i;

				while (j > 0 && list.get(j - 1).getEmail().compareTo(name) > 0) 
				{
					Application element = list.get(j - 1);
					list.set(j, element);

					j--;
				}
				list.set(j, app);
			}
		} else if (value1.equals("research_area")) {
			int i, j;
			String name;
			Application app;
			for (i = 1; i < list.size(); i++) 
			{
				name = list.get(i).getResearchArea();
				app = list.get(i);
				j = i;

				while (j > 0 && list.get(j - 1).getResearchArea().compareTo(name) > 0) 
				{
					Application element = list.get(j - 1);
					list.set(j, element);

					j--;
				}
				list.set(j, app);
			}
		} else if (value1.equals("undergraduate_schoool")) {
			int i, j;
			String name;
			Application app;
			for (i = 1; i < list.size(); i++) 
			{
				name = list.get(i).getUgInst();
				app = list.get(i);
				j = i;

				while (j > 0 && list.get(j - 1).getUgInst().compareTo(name) > 0) 
				{
					Application element = list.get(j - 1);
					list.set(j, element);

					j--;
				}
				list.set(j, app);
			}
		} else if (value2.equals("gpa")) {
			int i, j;
			String name;
			Application app;
			for (i = 1; i < list.size(); i++) 
			{
				name = list.get(i).getUgGpa();
				app = list.get(i);
				j = i;

				while (j > 0 && list.get(j - 1).getUgGpa().compareTo(name) > 0) 
				{
					Application element = list.get(j - 1);
					list.set(j, element);

					j--;
				}
				list.set(j, app);
			}
		}
		return list;
	}
	
	public static Statement connect(){
		try{
			Class.forName(driver);
			conn = (Connection) DriverManager.getConnection(dbUrl, dbUser, dbPassword);
			Statement stmt = (Statement) conn.createStatement();
			
			return stmt;
		}catch(Exception ex){return null;}
	}
}